import numpy as np
from django.shortcuts import render, redirect

from app01.mysql_opt import execute_query, execute_command
from app01.utils import split_strings_in_dict_list


# Create your views here.
def dianying(request):
    page = request.GET.get('page')
    if page is None or not page:
        page = 1
    page = int(page)
    user_id = request.GET.get('user_id')
    if user_id is None or not user_id:
        cur_user = execute_query(f"select cast(id as CHAR) id,username from users limit 1;")
    else:
        cur_user = execute_query(f"select cast(id as CHAR) id,username from users where id={user_id};")
    result_user = execute_query(f"select * from users")
    total_num = execute_query(f"select count(1) c from zhengzaireying;")[0]['c']
    result_dianying = execute_query(
        f"select t1.id,t1.fengmian,t1.dymc,t1.detail,GROUP_CONCAT(t2.userid) gc,count(1) c from zhengzaireying t1 left join storeup t2 on t1.id=t2.refid GROUP BY t1.id limit 8 offset {(page - 1) * 8};")
    result_dianying = split_strings_in_dict_list(result_dianying)
    return render(request, 'dianying.html',
                  {
                      'cur_user': cur_user[0],
                      'result_user': result_user,
                      'result_dianying': result_dianying,
                      'cur_page': page,
                      'last_page': int(total_num / 8) + 1,
                      'pages': list(range(1, int(total_num / 8) + 2)),
                      'active_1': 'active'
                  })


# Create your views here.
def shoucang(request):
    page = request.GET.get('page')
    if page is None or not page:
        page = 1
    page = int(page)
    user_id = request.GET.get('user_id')
    if user_id is None or not user_id:
        cur_user = execute_query(f"select * from users limit 1;")
    else:
        cur_user = execute_query(f"select * from users where id={user_id};")
    result_user = execute_query(f"select * from users")
    total_num = execute_query(
        f"select count(1) c from storeup t1 left join zhengzaireying t2 on t1.refid=t2.id where t1.userid={cur_user[0]['id']} order by t1.addtime desc;")[
        0]['c']
    result_shoucang = execute_query(
        f"select * from storeup t1 left join zhengzaireying t2 on t1.refid=t2.id where t1.userid={cur_user[0]['id']} order by t1.addtime desc limit 8 offset {(page - 1) * 8};")
    return render(request, 'shoucang.html',
                  {
                      'cur_user': cur_user[0],
                      'result_user': result_user,
                      'result_shoucang': result_shoucang,
                      'cur_page': page,
                      'last_page': int(total_num / 8) + 1,
                      'pages': list(range(1, int(total_num / 8) + 2)),
                      'active_2': 'active'
                  })


# Create your views here.
def tuijian(request):
    user_id = request.GET.get('user_id')
    if user_id == None:
        cur_user = execute_query(f"select * from users limit 1;")
    else:
        cur_user = execute_query(f"select * from users where id={user_id};")
    result_user = execute_query(f"select * from users")

    total_num = execute_query(
        f"select count(1) c from storeup t1 left join zhengzaireying t2 on t1.refid=t2.id where t1.userid={cur_user[0]['id']} order by t1.addtime desc;")[
        0]['c']
    result_shoucang = execute_query(
        f"select * from storeup t1 left join zhengzaireying t2 on t1.refid=t2.id where t1.userid={cur_user[0]['id']} order by t1.addtime desc;")
    shouchang_dymc = [i['dymc'] for i in result_shoucang]
    dymc_group = execute_query(
        f"select GROUP_CONCAT(t2.dymc) gc from storeup t1 left join zhengzaireying t2 on t1.refid=t2.id GROUP BY t1.userid;")
    from efficient_apriori import apriori
    # transactions = [('eggs', 'bacon', 'soup'),
    #                 ('eggs', 'bacon', 'apple'),
    #                 ('soup', 'bacon', 'banana')]
    transactions = []
    for i in dymc_group:
        transactions.append(tuple(i['gc'].split(',')))
    itemsets, rules = apriori(transactions, min_support=0.5, min_confidence=1)
    # Print out every rule with 2 items on the left hand side,
    # 1 item on the right hand side, sorted by lift
    # rules_filter = filter(lambda r: len(r.lhs) == 1, rules)
    rules_filter = rules
    rules_sort = [rule for rule in sorted(rules_filter, key=lambda r: r.lift)]
    rules_sort.reverse()
    result_rules = []
    for i in rules_sort:
        for j in i.lhs:
            if j in shouchang_dymc:
                result_rules.append(i)
                break
    result_tuijian_dict = dict()
    for i in result_rules:
        for j in i.rhs:
            # 排除重复
            if j not in shouchang_dymc:
                if j in result_tuijian_dict.keys():
                    result_tuijian_dict[j] += i.lift
                else:
                    result_tuijian_dict[j] = i.lift
    result_tuijian = sorted(result_tuijian_dict.items(), key=lambda x: x[1], reverse=True)
    return render(request, 'tuijian.html',
                  {
                      'cur_user': cur_user[0],
                      'result_user': result_user,
                      'result_shoucang': result_shoucang[:int(total_num / 4) + 1],
                      'total_num': total_num,
                      'result_tuijian': result_tuijian,
                      'result_rules': result_rules,
                      'active_4': 'active'
                  })


def dianying_detail(request):
    id = request.GET.get('id')
    dianying = execute_query(f"select * from zhengzaireying where id={id};")
    user_id = request.GET.get('user_id')
    if user_id is None or not user_id:
        cur_user = execute_query(f"select cast(id as CHAR) id,username from users limit 1;")
    else:
        cur_user = execute_query(f"select cast(id as CHAR) id,username from users where id={user_id};")
    result_user = execute_query(f"select * from users")
    return render(request, 'dianying_detail.html',
                  {
                      'cur_user': cur_user[0],
                      'result_user': result_user,
                      'dianying': dianying[0]
                  })


def shoucang_add(request):
    user_id = request.GET.get('user_id')
    refid = request.GET.get('refid')
    execute_command(f"INSERT INTO `storeup` (`userid`, `refid`) VALUE ({user_id}, {refid});")
    return redirect(f'/shoucang?user_id={user_id}')


def shoucang_del(request):
    user_id = request.GET.get('user_id')
    refid = request.GET.get('refid')
    execute_command(f"delete from  `storeup` where userid={user_id} and refid={refid};")
    return redirect(f'/shoucang?user_id={user_id}')


def echart(request):
    type_dict = {"leixing": "类型", "yuyan": "语言", "nianfen": "年份"}
    user_id = request.GET.get('user_id')
    type = request.GET.get('type')
    if user_id is None or not user_id:
        cur_user = execute_query(f"select cast(id as CHAR) id,username from users limit 1;")
    else:
        cur_user = execute_query(f"select cast(id as CHAR) id,username from users where id={user_id};")
    result_user = execute_query(f"select * from users")
    if type == 'nianfen':
        type_count = execute_query(
            f"select YEAR(syrq) k,count(1) v from storeup t1 left join zhengzaireying t2 on t1.refid=t2.id where t1.userid={cur_user[0]['id']} GROUP BY YEAR(syrq) ORDER BY v desc;")
    else:
        type_count = execute_query(
            f"select {type} k,count(1) v from storeup t1 left join zhengzaireying t2 on t1.refid=t2.id where t1.userid={cur_user[0]['id']} GROUP BY {type} ORDER BY v desc;")
    keys = []
    values = []
    for i in type_count:
        keys.append(i['k'])
        values.append(i['v'])
    return render(request, 'echart.html',
                  {
                      'cur_user': cur_user[0],
                      'result_user': result_user,
                      'active_3': 'active',
                      'keys': keys,
                      'values': values,
                      'title': f'按{type_dict[type]}统计',
                      'type': type_dict[type]
                  })


def tuijian_cf(request):
    user_id = request.GET.get('user_id')
    if user_id == None:
        cur_user = execute_query(f"select * from users limit 1;")
    else:
        cur_user = execute_query(f"select * from users where id={user_id};")

    result_shoucang = execute_query(
        f"select * from storeup t1 left join zhengzaireying t2 on t1.refid=t2.id where t1.userid={cur_user[0]['id']} order by t1.addtime desc;")
    shouchang_dymc = [i['dymc'] for i in result_shoucang]
    result_user = execute_query(f"select * from users")
    ratings = []
    # 查询用户收藏的所有电影(拼成全量矩阵)
    for i in result_user:
        query_result = execute_query(
            f"select CASE WHEN t2.userid IS NOT NULL THEN 1 ELSE 0 END AS is_shoucang from zhengzaireying t1 left join storeup t2 on t1.id=t2.refid and t2.userid={i['id']} order by t1.id;")
        query_result = [i['is_shoucang'] for i in query_result]
        ratings.append(query_result)
    ratings = np.array(ratings)

    # 计算用户之间的Jaccard相似度
    def jaccard_similarity(user1, user2):
        intersect = np.sum(user1 * user2)
        union = np.sum(user1) + np.sum(user2) - intersect
        return intersect / union if union > 0 else 0

    # 计算整个评分矩阵的Jaccard相似度矩阵
    user_similarity = np.zeros((ratings.shape[0], ratings.shape[0]))
    for i in range(ratings.shape[0]):
        for j in range(ratings.shape[0]):
            if i != j:
                user_similarity[i, j] = jaccard_similarity(ratings[i], ratings[j])
            else:
                user_similarity[i, j] = 1  # 用户与自己的相似度为1
    print(user_similarity)

    similarities = user_similarity[cur_user[0]['id'] - 1]
    similar_users = np.argsort(similarities)[::-1]  # 按降序排序，得到索引
    result_tuijian = []
    easy_tuijain=set()
    #
    # query_result = execute_query(
    #     f"select GROUP_CONCAT(t2.dymc),t3.username,t3.id from storeup t1 left join zhengzaireying t2 on t1.refid=t2.id left join users t3 on t3.id=t1.userid where t1.userid in ({','.join([str(i) for i in similar_users[1:6]])}) GROUP BY t1.userid;")
    for i in similar_users[1:6]:
        query_result = execute_query(
            f"select GROUP_CONCAT(t2.dymc) dymc_str,t3.username,t3.id from storeup t1 left join zhengzaireying t2 on t1.refid=t2.id left join users t3 on t3.id=t1.userid where t1.userid ={i+1} GROUP BY t1.userid;")[0]
        result = list(filter(lambda x: x not in shouchang_dymc, query_result['dymc_str'].split(',')))
        easy_tuijain.update(result)
        query_result['dymc_str_other'] = '，'.join(result)
        query_result['sim_value'] = similarities[i]
        # 排除自己，取234,目前用户id就是序号，以后用序号反查用户id
        result_tuijian.append(query_result)
    total_num = execute_query(
        f"select count(1) c from storeup t1 left join zhengzaireying t2 on t1.refid=t2.id where t1.userid={cur_user[0]['id']} order by t1.addtime desc;")[
        0]['c']
    result_shoucang = execute_query(
        f"select * from storeup t1 left join zhengzaireying t2 on t1.refid=t2.id where t1.userid={cur_user[0]['id']} order by t1.addtime desc;")
    result_tuijian
    return render(request, 'tuijian_cf.html',
                  {
                      'cur_user': cur_user[0],
                      'result_user': result_user,
                      'result_shoucang': result_shoucang[:int(total_num / 4) + 1],
                      'total_num': total_num,
                      'result_tuijian': result_tuijian,
                      'easy_tuijain': easy_tuijain,
                      'active_4': 'active'
                  })
